import time
import subprocess
import pymysql
from Injection_valve_runze import insert_error_num  # 记录对应步骤，方便跳转
import os

# 定义数据库连接信息
con_initial = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='!qAz2WsX3edc',
    db='ph',
    charset='utf8'
)
cur = con_initial.cursor()
cur.execute("SELECT MAX(ID) FROM DHT")
initial_count = int(cur.fetchone()[0])
cur.close()


def sql_query_s14_9_t7():  # 储存跳到s14第九步的计时
    con_t7 = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='!qAz2WsX3edc',
        db='ph',
        charset='utf8'
    )
    cur_t7 = con_t7.cursor()
    cur_t7.execute("select time_storage from time_storage where name ='s14_9_t7' order by id desc limit 1")
    s14_9_t7 = int(cur_t7.fetchone()[0])
    cur_t7.close()
    return s14_9_t7


def sql_test_del_main_start():  # 储存当前s14的时间参数
    con_del_main_start = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='!qAz2WsX3edc',
        db='ph',
        charset='utf8'
    )
    cur_del_main_start = con_del_main_start.cursor()
    sql2 = "DELETE FROM test WHERE message = 'main_start'"
    cur_del_main_start.execute(sql2)
    con_del_main_start.commit()
    cur_del_main_start.close()
    return


def sql_DHT_jump_time():
    con_jump = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='!qAz2WsX3edc',
        db='ph',
        charset='utf8'
    )
    cur_jump = con_jump.cursor()
    cur_jump.execute("SELECT temperature FROM DHT WHERE address = 'jump_9'")
    s14_9_t7 = int(cur_jump.fetchone()[0])
    cur_jump.close()
    return s14_9_t7


def current_count_t():
    con_count_t = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='!qAz2WsX3edc',
        db='ph',
        charset='utf8'
    )
    count_t = con_count_t.cursor()
    count_t.execute("SELECT MAX(ID) FROM DHT")
    result = count_t.fetchone()[0]
    count_t.close()
    return result


def sql_s14_time_storage(name, time_storage):  # 储存当前s14的时间参数
    con_s14_time = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='!qAz2WsX3edc',
        db='ph',
        charset='utf8'
    )
    cur_s14_time = con_s14_time.cursor()
    time1 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    cur_s14_time.executemany("INSERT time_storage(time ,name, time_storage) VALUE(%s,%s,%s)",
                             [(time1, name, time_storage)])
    cur_s14_time.execute("SELECT min(id) FROM time_storage ")
    min_mode = int(cur_s14_time.fetchone()[0])
    cur_s14_time.execute("SELECT max(id) FROM time_storage ")
    max_mode = int(cur_s14_time.fetchone()[0])
    if (max_mode - min_mode) >= 1000:
        sql2 = ("delete FROM time_storage " + "WHERE id <= %s " % (max_mode - 100) + " and id >= %s" % min_mode)
        cur_s14_time.execute(sql2)
    con_s14_time.commit()
    cur_s14_time.close()
    return


def sql_test(message):  # 储存当前s14的时间参数
    con_test = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='!qAz2WsX3edc',
        db='ph',
        charset='utf8'
    )
    cur_test = con_test.cursor()
    time1 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    cur_test.executemany("INSERT test(time ,message) VALUE(%s,%s)",
                         [(time1, message)])

    con_test.commit()
    cur_test.close()
    return


def inquire_main_restart():
    con_count_t = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='!qAz2WsX3edc',
        db='ph',
        charset='utf8'
    )
    count_t = con_count_t.cursor()
    count_t.execute("SELECT COUNT(*) FROM test WHERE message = 'main_restart'")
    result = int(count_t.fetchone()[0])
    count_t.close()
    return result


def del_test_to_1000():
    mydb = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='!qAz2WsX3edc',
        db='ph',
        charset='utf8'
    )
    mycursor = mydb.cursor()
    mycursor.execute("SELECT COUNT(*) FROM test")
    count = mycursor.fetchone()[0]  # 获取查询结果中的第一个值
    # 如果行数超过 10000，只保留后一千行
    if count > 10000:
        # 获取需要删除的行数
        delete_count = count - 1000
        # 删除 test 表中前 delete_count 行的数据
        mycursor.execute(f"DELETE FROM test ORDER BY id LIMIT {delete_count}")
        # 提交更改
        mydb.commit()
    # 关闭数据库连接
    mydb.close()


# 开始监测表
while True:
    # 获取当前行数

    current_count = int(current_count_t())
    print("初始行数", initial_count)
    print("目前行数", current_count)

    if current_count > 10000:  # 如果超过一万行就删除到只剩一千行
        del_test_to_1000()

    if inquire_main_restart() > 0:  # 检测是否重启成功
        print("main in restart")
        start_time = time.time()
        # 在五分钟内检查是否有新数据
        while True:
            if inquire_main_restart() > 0 and time.time() - start_time > 300:
                sql_test("main_start_error")
                os.system('sudo reboot')
                exit()
            elif inquire_main_restart() <= 0:
                sql_test("main_start_normal")
                break

    # 检查是否有新数据
    if current_count > initial_count:
        sql_test("measurement_Start")
        while 1:
            print("Table is being updated.")
            start_time = time.time()
            # 在五分钟内检查是否有新数据
            while True:
                # 获取当前行数
                time.sleep(5)
                current_count = int(current_count_t())

                # 如果有新数据，退出循环
                if current_count > initial_count:
                    sql_test("measurement_updated")
                    break

                # 如果已经过了五分钟没有新数据诞生，重启树莓派
                if time.time() - start_time > 300:
                    print("No new data in the last 5 minutes. Running specified file.")
                    insert_error_num(7, 's14')
                    insert_error_num(0, 's15')
                    insert_error_num(0, 's3')
                    sql_s14_time_storage("s14_9_t7", int(sql_query_s14_9_t7()))
                    sql_test("measurement_error")
                    sql_test_del_main_start()
                    sql_test("main_restart")
                    os.system('sudo reboot')
                    exit()
                try:
                    if sql_DHT_jump_time() > 0:  # 若是跳转到其他步骤，则正常退出
                        sql_test("measurement_over")
                        exit()
                except Exception as e:
                    # 处理所有异常错误
                    pass

            # 更新初始行数
            initial_count = current_count

    # 每五秒钟检查一次表是否被更新
    time.sleep(5)
